package org.colafries.metadata.driver.impl;

import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.colafries.common.assertion.Asserts;
import org.colafries.common.metadata.Column;
import org.colafries.common.metadata.Schema;
import org.colafries.common.metadata.Table;
import org.colafries.metadata.driver.DataSourcePool;
import org.colafries.metadata.driver.Driver;
import org.colafries.metadata.entity.domain.DriverConfig;
import org.colafries.metadata.result.JdbcSelectResult;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * @author Ann
 * @version v1.0
 * @ClassName AbstractJdbcDriver
 * @Description
 * @date Created in 2024/7/26
 **/
@Slf4j
public abstract class AbstractJdbcDriver implements Driver {

    private DruidDataSource druidDataSource;


    /**
     * @param driverConfig
     * @return
     */
    @Override
    public Driver build(DriverConfig driverConfig) {
        String key = driverConfig.getDataSourceKey();
        if (!DataSourcePool.exist(key)) {
            //创建连接池
            DruidDataSource newDataSource = createDataSource(driverConfig);
            DataSourcePool.push(driverConfig.getDataSourceKey(), newDataSource);
            this.druidDataSource = newDataSource;
        }
        this.druidDataSource = DataSourcePool.get(key);
        return this;
    }

    /**
     * @return
     */
    @Override
    public Boolean test( String user, String pwd) throws Exception {
        try {
            Class.forName(getDriverClass());
            String url = druidDataSource.getUrl();
            DriverManager.getConnection(url, user, pwd).close();
        } catch (Exception e) {
            log.info("Jdbc链接测试失败！错误信息为：{}", e.getMessage());
            throw e;
        }
        return true;
    }

    public DruidDataSource createDataSource(DriverConfig driverConfig) {
        DruidDataSource ds = new DruidDataSource();
        ds.setName(driverConfig.getDataSourceKey());
        ds.setUrl(driverConfig.getUrl().replace("{SubProtocol}", getSubProtocol()));
        ds.setDriverClassName(getDriverClass());
        ds.setUsername(driverConfig.getUserName());
        ds.setPassword(driverConfig.getPassWord());
        ds.setValidationQuery("select 1");
        ds.setTestWhileIdle(true);
        ds.setBreakAfterAcquireFailure(true);
        ds.setFailFast(true);
        ds.setInitialSize(1);
        ds.setMaxActive(8);
        ds.setMinIdle(5);
        return ds;
    }

    /**
     * Schemas
     *
     * @return
     */
    public List<Schema> listSchemas() {
        List<Schema> schemas = new ArrayList<>();
        String schemasSql = getDBQuery().schemaAllSql();
        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(schemasSql);
             ResultSet results = preparedStatement.executeQuery()
        ) {
            while (results.next()) {
                String schemaName = results.getString(getDBQuery().schemaName());
                if (!Strings.isNullOrEmpty(schemaName)) {
                    Schema schema = new Schema(schemaName);
                    schemas.add(schema);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return schemas;
    }

    /**
     * 根据schema获取Table
     *
     * @param schemaName
     * @return
     */
    @Override
    public List<Table> listTables(String schemaName) {
        List<Table> tableList = new ArrayList<>();
        String listTableSql = getDBQuery().tablesSql(schemaName);

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(listTableSql);
             ResultSet results = preparedStatement.executeQuery()
        ) {
            ResultSetMetaData metaData = results.getMetaData();
            List<String> columnList = Lists.newArrayList();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                columnList.add(metaData.getColumnLabel(i));
            }
            while (results.next()) {
                String tableName = results.getString(getDBQuery().tableName());
                if (!Strings.isNullOrEmpty(tableName)) {
                    Table tableInfo = new Table(tableName);

//                    if (columnList.contains(getDBQuery().tableComment())) {
//                        tableInfo.setComment(results.getString(getDBQuery().tableComment()));
//                    }
//                    tableInfo.setSchema(schemaName);
//                    if (columnList.contains(getDBQuery().tableType())) {
//                        tableInfo.setType(results.getString(getDBQuery().tableType()));
//                    }
//                    if (columnList.contains(getDBQuery().catalogName())) {
//                        tableInfo.setCatalog(results.getString(getDBQuery().catalogName()));
//                    }
//                    if (columnList.contains(getDBQuery().engine())) {
//                        tableInfo.setEngine(results.getString(getDBQuery().engine()));
//                    }
//                    if (columnList.contains(getDBQuery().options())) {
//                        tableInfo.setOptions(results.getString(getDBQuery().options()));
//                    }
//                    if (columnList.contains(getDBQuery().rows())) {
//                        tableInfo.setRows(results.getLong(getDBQuery().rows()));
//                    }
//                    if (columnList.contains(getDBQuery().createTime())) {
//                        tableInfo.setCreateTime(results.getTimestamp(getDBQuery().createTime()));
//                    }
//                    if (columnList.contains(getDBQuery().updateTime())) {
//                        tableInfo.setUpdateTime(results.getTimestamp(getDBQuery().updateTime()));
//                    }
                    tableList.add(tableInfo);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return tableList;
    }

    @Override
    public List<Column> getTableSchema(String schemaName, String tableName) {
        List<Column> columns = new ArrayList<>();

        String listColumnSql = getDBQuery().columnsSql(schemaName, tableName);
        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(listColumnSql);
             ResultSet results = preparedStatement.executeQuery()
        ) {
            ResultSetMetaData metaData = results.getMetaData();
            List<String> columnList = new ArrayList<>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                columnList.add(metaData.getColumnLabel(i));
            }
            while (results.next()) {
                Column field = new Column();
                String columnName = results.getString(getDBQuery().columnName());
                field.setName(columnName);
                if (columnList.contains(getDBQuery().columnKey())) {
                    String key = results.getString(getDBQuery().columnKey());
                    field.setKeyFlag(!Strings.isNullOrEmpty(key) && StrUtil.equalsIgnoreCase(getDBQuery().isPK(), key));
                }
                //column type
                if (columnList.contains(getDBQuery().columnType())) {
                    String columnType = results.getString(getDBQuery().columnType());
                    if (columnType.contains("(")) {
                        String type = columnType.replaceAll("\\(.*\\)", "");
                        if (!columnType.contains(",")) {
                            Integer length = Integer.valueOf(columnType.replaceAll("\\D", ""));
                            field.setLength(length);
                        } else {
                            // some database does not have precision
                            if (getDBQuery().precision() != null) {
                                // 例如浮点类型的长度和精度是一样的，decimal(10,2)
                                field.setLength(results.getInt(getDBQuery().precision()));
                            }
                        }
                        field.setType(type);
                    } else {
                        field.setType(columnType);
                    }
                }
                // column comment
                if (columnList.contains(getDBQuery().columnComment())
                        && !Strings.isNullOrEmpty(results.getString(getDBQuery().columnComment()))) {
                    String columnComment = results.getString(getDBQuery().columnComment()).replaceAll("[\"']", "");
                    field.setComment(columnComment);
                }
                // column length
                if (columnList.contains(getDBQuery().columnLength())) {
                    int length = results.getInt(getDBQuery().columnLength());
                    if (!results.wasNull()) {
                        field.setLength(length);
                    }
                }
                // 是否允许为 NULL
                if (columnList.contains(getDBQuery().isNullable())) {
                    field.setNullable(StrUtil.equalsIgnoreCase(results.getString(getDBQuery().isNullable()),
                            getDBQuery().nullableValue()));
                }
                // TODO ... ...
                // 字符集名称
                if (columnList.contains(getDBQuery().characterSet())) {
                    field.setCharacterSet(results.getString(getDBQuery().characterSet()));
                }
                // 排序规则
                if (columnList.contains(getDBQuery().collation())) {
                    field.setCollation(results.getString(getDBQuery().collation()));
                }
                // 字段序号
                if (columnList.contains(getDBQuery().columnPosition())) {
                    field.setPosition(results.getInt(getDBQuery().columnPosition()));
                }
                // 精度
                if (columnList.contains(getDBQuery().precision())) {
                    field.setPrecision(results.getInt(getDBQuery().precision()));
                }
                // 小数范围
                if (columnList.contains(getDBQuery().scale())) {
                    field.setScale(results.getInt(getDBQuery().scale()));
                }
                // 默认值
                if (columnList.contains(getDBQuery().defaultValue())) {
                    field.setDefaultValue(results.getString(getDBQuery().defaultValue()));
                }
                // 主键自增
                if (columnList.contains(getDBQuery().autoIncrement())) {
                    field.setAutoIncrement(
                            StrUtil.equalsIgnoreCase(results.getString(getDBQuery().autoIncrement()), "auto_increment"));
                }
                // java type
                field.setJavaType(getTypeConvert().convert(field).toString());
                columns.add(field);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return columns;
    }

    /**
     * @param sql
     * @return
     */
    @Override
    public JdbcSelectResult queryPage(String sql, Integer current, Integer size) {
        if (Strings.isNullOrEmpty(sql)) {
            throw new RuntimeException("sql为空");
        }
        JdbcSelectResult result = new JdbcSelectResult();
        current = current != null ? current : 1;
        result.setPage(current);
        // size < 0 则查询全部
        size = size > 0 ? size : 99999999;

        List<LinkedHashMap<String, Object>> dataList = Lists.newArrayList();
        List<Column> columns = Lists.newArrayList();
        // Count SQL
        String countSql = String.format("select COUNT(*) AS total from (%s) AS SQL_COUNT", sql);
        // Page SQL
        // Page offset 起始位置 size 返回的行数
        int offset = (current - 1) * size;

        String pageSql = String.format("%s LIMIT %s,%s", sql, offset, size);
        result.setQuerySql(pageSql);

        // 记录开始时间
        long starTime = System.currentTimeMillis();
        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(pageSql);
             ResultSet results = preparedStatement.executeQuery();
             ResultSet countResults = conn.createStatement().executeQuery(countSql);
        ) {
            // Total
            int total = 0;
            if (countResults.next()) {
                total = countResults.getInt("total");
            }
            result.setTotal(total);
            // Columns
            ResultSetMetaData metaData = results.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                Column column = new Column();
                column.setName(metaData.getColumnLabel(i));
                column.setType(metaData.getColumnTypeName(i));
                column.setJavaType(getTypeConvert().convert(column).toString());
                columns.add(column);
//                columnNameList.add(metaData.getColumnLabel(i));
            }
            result.setColumns(columns);

            // RowData
            while (results.next()) {
                LinkedHashMap<String, Object> data = new LinkedHashMap<>();
                for (Column column : columns) {
                    Object value = getTypeConvert().convertValue(
                            results,
                            column.getName(),
                            column.getType()
                    );
                    if (Asserts.isNotNull(value)) {
                        data.put(column.getName(), value.toString());
                    } else {
                        data.put(column.getName(), null);
                    }
                }
                dataList.add(data);
            }
            long endTime = System.currentTimeMillis();
            result.setExecTime(endTime - starTime);
            result.setRowData(dataList);
            result.setSize(dataList.size());

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result;
    }


    /**
     * 查询sql结果schema
     *
     * @param sql
     * @return java.util.List<org..colafries.admin.entity.vo.ColumnRspVO>
     * @author colafries
     * @Date 2024/4/3
     */
    @Override
    public List<Column> querySchema(String sql) {
        if (Strings.isNullOrEmpty(sql)) {
            throw new RuntimeException("sql为空");
        }

        List<Column> columns = Lists.newArrayList();
        // Page SQL
        String pageSql = String.format("select * from (%s) AS SQL_PAGE LIMIT %s,%s", sql, 0, 1);
        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(pageSql);
             ResultSet results = preparedStatement.executeQuery();
        ) {
            // Columns
            ResultSetMetaData metaData = results.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                Column column = new Column();
                column.setName(metaData.getColumnLabel(i));
                column.setType(metaData.getColumnTypeName(i));
                column.setJavaType(getTypeConvert().convert(column).toString());
//                column.setComment(metaData.);
                columns.add(column);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return columns;
    }
}
